﻿/* {dbowner}[{dbprofix}Version] */
/**************************************/
PRINT N'Creating {dbowner}[{dbprofix}Version]'
GO

CREATE TABLE {dbowner}[{dbprofix}Version]
(
	[VersionId] [int] NOT NULL IDENTITY(1, 1),
	[Major] [int] NOT NULL,
	[Minor] [int] NOT NULL,
	[Build] [int] NOT NULL,
	[Name] [nvarchar] (50) NULL,
	[CreatedDate] [datetime] NOT NULL
)
GO

PRINT N'Creating primary key [PK_{dbprofix}Version] on {dbowner}[{dbprofix}Version]'
GO

ALTER TABLE {dbowner}[{dbprofix}Version] ADD CONSTRAINT [PK_{dbprofix}Version] PRIMARY KEY CLUSTERED  ([VersionId])
GO

PRINT N'Creating {dbowner}[{dbprofix}UpdateDatabaseVersion]'
GO


create procedure {dbowner}{dbprofix}UpdateDatabaseVersion

	@Major  int,
	@Minor  int,
	@Build  int,
	@Name	nvarchar(50)

AS

	INSERT INTO {dbowner}{dbprofix}Version (
		Major,
		Minor,
		Build,
		[Name],
		CreatedDate
	)
		VALUES (
			@Major,
			@Minor,
			@Build,
			@Name,
			getdate()
		)
GO

PRINT N'Creating {dbowner}[{dbprofix}GetDatabaseVersion]'
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{dbowner}[{dbprofix}GetDatabaseVersion]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
  DROP PROCEDURE {dbowner}[{dbprofix}GetDatabaseVersion]
GO

create procedure {dbowner}{dbprofix}GetDatabaseVersion
as
select Major,
       Minor,
       Build
from   {dbowner}{dbprofix}Version 
where  VersionId = ( select max(VersionId) from {dbowner}{dbprofix}Version )
GO